/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.marlboroug.articlesordered;

import com.marlboroug.entity.Articles;
import com.marlboroug.entity.Articlesordered;
import com.marlboroug.entity.Orders;
import java.util.Date;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;


@Stateless
public class ArticlesOrderedManager implements ArticlesOrderedManagerRemote {
    
    @PersistenceContext(unitName = "Marlboroug-ejbPU")
    EntityManager em;    
    
    /** Permet de passer une commande
     * 
     * @param list Liste des articles à commander avec la quantité
     * @return Un boolean pour confirmer la commande
     */
    @Override
    public Boolean OrderPlaced(List<Articlesordered> list){
        //On INSERT dans Orders
        Orders add_order = new Orders();
        Date date = new Date(); //Récupère la date courante
        add_order.setDateReceived(date); // Ajoute Date courante 
        add_order.setStatus(1); //Ajoute Statut de la commande  
        add_order.setGrossAmount(list.get(0).getIdOrders().getGrossAmount()); //Ajoute TTC
        add_order.setNetAmount(list.get(0).getIdOrders().getNetAmount()); //Ajoute HT
        add_order.setIdUsers(list.get(0).getIdOrders().getIdUsers()); //Ajoute ID User        
        em.persist(add_order); //Fait l'INSERT
        
        //On INSERT dans Articles_ordered
        /*
        SELECT Orders.ID_orders
        FROM Orders 
        ORDER BY Orders.ID_orders DESC
        LIMIT 1
        */
        int boucle = 0; //Pour la boucle
        //Récupère l'ID de Orders
        String q = "SELECT o FROM Orders o ORDER BY o.iDorders DESC"; 
        TypedQuery<Orders> query = em.createQuery(q, Orders.class);
        query.setMaxResults(1);            
        Orders id_orders = query.getSingleResult();
        for (Articlesordered m : list) {     

            em.createNativeQuery("INSERT INTO Articles_ordered (quantity_article, id_orders, id_articles) VALUES(?, ?, ?)")
                .setParameter(1, list.get(boucle).getQuantityArticle()) //Ajoute la quantité     
                .setParameter(2, id_orders.getIDorders()) //Ajoute l'ID d'Orders
                .setParameter(3, list.get(boucle).getIdArticles().getIDarticles())  //Ajoute l'ID d'Articles 
                .executeUpdate();
            
            //Met à jour la table Articles pour chaque articles
            //Récupère le nombre en stock pour vérifier qu'il ne sera pas négatif dans la requête
            String q2 = "SELECT ar FROM Articles ar WHERE ar.iDarticles = :id"; 
            TypedQuery<Articles> query2 = em.createQuery(q2, Articles.class);     
            query2.setParameter("id", list.get(boucle).getIdArticles().getIDarticles());
            Articles result_articles = query2.getSingleResult();
            if(result_articles.getQuantityStock()- list.get(boucle).getQuantityArticle() < 0) //Si stock négatif alors = 0
                em.createNativeQuery("UPDATE Articles SET quantity_stock = 0, sold = sold+1 WHERE ID_articles = ?")
                    .setParameter(1, list.get(boucle).getIdArticles().getIDarticles()) //Recherche avec ID  
                    .executeUpdate();
            else //Le stock sera >= à 0
                em.createNativeQuery("UPDATE Articles SET quantity_stock = quantity_stock-?, sold = sold+1 WHERE ID_articles = ?")
                    .setParameter(1, list.get(boucle).getQuantityArticle()) //Retranche la quantité commandée 
                    .setParameter(2, list.get(boucle).getIdArticles().getIDarticles()) //Recherche avec ID 
                    .executeUpdate();
            
            //Met à jour l'état des stock si le nombre du produit est en dessous de la limit
            if(result_articles.getQuantityStock() < result_articles.getLimitStock())
                em.createNativeQuery("UPDATE Articles SET state = 4 WHERE ID_articles = ?") //En réapprovisionnement
                    .setParameter(1, list.get(boucle).getIdArticles().getIDarticles()) //Recherche avec ID  
                    .executeUpdate();
            boucle++;
        }             
  

        return true;
    }
    
    /** Permet de lister les commandes d'un utilisateur
     * 
     * @param ID_user un ID d'un utilisateur
     * @return Une liste des commandes d'un utilisateur avec ses paramètres
     */
    @Override //Liste tous sauf authors 
    public List<Articlesordered> OrdersList(Long ID_user){
        /*
        SELECT *
        FROM Articles_ordered 
        INNER JOIN Orders ON Articles_ordered.id_orders = Orders.ID_orders
        INNER JOIN Users ON Orders.id_users = Users.ID_users
        INNER JOIN Articles ON Articles_ordered.id_articles = Articles.ID_articles
        INNER JOIN Categories ON Articles.id_categories = Categories.ID_categories
        INNER JOIN Editors ON Articles.id_editors = Editors.ID_editors
        WHERE Users.ID_users = 4
        ORDER BY Orders.ID_orders ASC  
         */
        String q = "SELECT ao "
                + "FROM Articlesordered ao "
                + "INNER JOIN Orders ord "
                + "ON ao.idOrders.iDorders = ord.iDorders "
                + "INNER JOIN Users us "
                + "ON ord.idUsers.iDusers = us.iDusers "
                + "INNER JOIN Articles ar "
                + "ON ao.idArticles.iDarticles = ar.iDarticles "
                + "INNER JOIN Categories ca "
                + "ON ar.idCategories.iDcategories = ca.iDcategories "
                + "INNER JOIN Editors ed "
                + "ON ar.idEditors.iDeditors = ed.iDeditors "
                + "WHERE us.iDusers = :id "
                + "ORDER BY ord.iDorders ASC";
        TypedQuery<Articlesordered> query = em.createQuery(q, Articlesordered.class);  
        query.setParameter("id", ID_user);
        List<Articlesordered> order = query.getResultList();
        return order;
    }
    
    /** Permet de lister les commandes des utilisateurs
     * 
     * @return Une liste des commandes des utilisateurs avec ses paramètres
     */
    @Override //Liste tous sauf authors 
    public List<Articlesordered> findAll(){
        /*
        SELECT *
        FROM Articles_ordered 
        INNER JOIN Orders ON Articles_ordered.id_orders = Orders.ID_orders
        INNER JOIN Users ON Orders.id_users = Users.ID_users
        INNER JOIN Articles ON Articles_ordered.id_articles = Articles.ID_articles
        INNER JOIN Categories ON Articles.id_categories = Categories.ID_categories
        INNER JOIN Editors ON Articles.id_editors = Editors.ID_editors
        WHERE Users.ID_users = 4
        ORDER BY Orders.ID_orders ASC  
         */
        String q = "SELECT ao "
                + "FROM Articlesordered ao "
                + "INNER JOIN Orders ord "
                + "ON ao.idOrders.iDorders = ord.iDorders "
                + "INNER JOIN Users us "
                + "ON ord.idUsers.iDusers = us.iDusers "
                + "INNER JOIN Articles ar "
                + "ON ao.idArticles.iDarticles = ar.iDarticles "
                + "INNER JOIN Categories ca "
                + "ON ar.idCategories.iDcategories = ca.iDcategories "
                + "INNER JOIN Editors ed "
                + "ON ar.idEditors.iDeditors = ed.iDeditors "
                + "ORDER BY ord.iDorders ASC";
        TypedQuery<Articlesordered> query = em.createQuery(q, Articlesordered.class);  
        List<Articlesordered> order = query.getResultList();
        return order;
    }
}
